package cn.bestick.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;
import cn.bestick.model.Class;

/**
 * @author Bestick
 *
 *         be stick...
 *
 *         2015年9月9日 下午4:29:33
 */
public class ClassDao {

	/**
	 * 判断编号为gradeId的Class是否存在
	 * 
	 * @param con
	 * @param gradeId
	 * @return boolean
	 * @throws Exception
	 */
	public boolean existClassById(Connection con, int gradeId) throws Exception {
		String sql = "select * from t_class where gradeId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, gradeId);
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			return true;
		} else {
			return false;
		}
	}

	/**
	 * 获取班级List
	 * 
	 * @param con
	 * @return List<Class>
	 * @throws Exception
	 */
	public List<Class> classList(Connection con, Class s_class) throws Exception {
		List<Class> classList = new ArrayList<Class>();
		StringBuffer sb = new StringBuffer("select * from t_class t1,t_grade t2 where t1.gradeId=t2.gradeId");
		if (s_class !=null && (s_class.getGradeId() != -1)) {
			sb.append(" and t1.gradeId="+s_class.getGradeId());
		}
		PreparedStatement pstmt = con.prepareStatement(sb.toString());
		ResultSet rs = pstmt.executeQuery();
		while (rs.next()) {
			Class c = new Class();
			c.setClassId(rs.getInt("classId"));
			c.setClassName(rs.getString("className"));
			c.setClassDesc(rs.getString("classDesc"));
			c.setGradeId(rs.getInt("gradeId"));
			c.setGradeName(rs.getString("gradeName"));
			classList.add(c);
		}
		return classList;
	}

	/**
	 * 班级添加
	 * 
	 * @param con
	 * @param class
	 * @return int
	 * @throws Exception
	 */
	public int classAdd(Connection con, Class c) throws Exception {
		String sql = "insert into t_class values(null,?,?,?)";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, c.getClassName());
		pstmt.setInt(2, c.getGradeId());
		pstmt.setString(3, c.getClassDesc());
		return pstmt.executeUpdate();
	}

	/**
	 * 根据classId获取一个班级
	 * 
	 * @param con
	 * @param classId
	 * @return Class
	 * @throws Exception
	 */
	public Class getClassById(Connection con, int classId) throws Exception {
		Class c = null;
		String sql = "select * from t_class where classId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, classId);
		ResultSet rs = pstmt.executeQuery();
		if (rs.next()) {
			c = new Class();
			c.setClassId(rs.getInt("classId"));
			c.setClassName(rs.getString("className"));
			c.setGradeId(rs.getInt("gradeId"));
			c.setClassDesc(rs.getString("classDesc"));
		}
		return c;
	}

	/**
	 * 对现有班级进行更新操作
	 * 
	 * @param con
	 * @param c
	 * @return int
	 * @throws Exception
	 */
	public int classUpdate(Connection con, Class c) throws Exception {
		String sql = "update t_class set className=?,gradeId=?,classDesc=? where classId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setString(1, c.getClassName());
		pstmt.setInt(2, c.getGradeId());
		pstmt.setString(3, c.getClassDesc());
		pstmt.setInt(4, c.getClassId());
		return pstmt.executeUpdate();
	}

	/**
	 * 删除编号为classId的班级
	 * 
	 * @param con
	 * @param classId
	 * @return int
	 * @throws Exception
	 */
	public int classDelete(Connection con, int classId) throws Exception {
		String sql = "delete from t_class where classId=?";
		PreparedStatement pstmt = con.prepareStatement(sql);
		pstmt.setInt(1, classId);
		return pstmt.executeUpdate();
	}
}
